package com.kcs.dc.report.model.logic;

import java.sql.Connection;
import java.sql.SQLException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.kcs.common.framework.GenericDto;
import com.kcs.common.utils.ConnectionUtils;
import com.kcs.common.utils.Constant;
import com.kcs.common.utils.ConstantMessage;
import com.kcs.common.utils.EventFactory;
import com.kcs.common.utils.LogWriter;
import com.kcs.dc.report.model.dao.CommonDAO;
import com.kcs.dc.report.model.dao.userprofileDAO;
import com.kcs.dc.report.model.dao.model.utilGridInfoModel;
import com.kcs.dc.report.view.dto.MainMenuDto;
import com.kcs.dc.report.view.dto.UserProfileAddEditDto;
import com.kcs.dc.report.view.dto.UserProfileDto;

public class userprofileBL {

	private CommonDAO daoCommon = null;
	private userprofileDAO dao = null;
	private Connection conn = null;
	
	public userprofileBL() {
		daoCommon = new CommonDAO();
		dao = new userprofileDAO();
	}
	
	public GenericDto initEvent(UserProfileDto dto) throws Exception{
		
		LogWriter.debug("<=====Start init(UserProfile) Event=====>", Constant.LOGGER_APPLICATION);
		
		conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO);
		
		try{
			LogWriter.debug("Step 1 : Check Authorized to use page.", Constant.LOGGER_APPLICATION);
			
			if(!commonBL.checkAuthenUsePage(dto.getUserId(), conn, "config.sys")){
				
				dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_NOHAVE_AUTHEN_PAGE, null);	        	
	        	dto.setService_id("welcome");
	        	
	        	// have error
				MainMenuDto menuDto = new MainMenuDto();
				menuDto.populate(dto);
				return menuDto;	
	        	
			}else{
				// Get all user.
				LogWriter.debug("Step 2 : Get all user", Constant.LOGGER_APPLICATION);
				getUser(dto, conn);
				
				if (dto.getRow() != 0){
					// Have data ==> set hidden userId, userName for export EXCEL.
					dto.setHiduserIdsearch("");
					dto.setHiduserNameSearch("");
				}
				
				dto.setService_id("userprofile");
			}
	
		}catch(Exception e){
			LogWriter.error("userprofileBL [Method : initEvent] : " + e.getMessage(), Constant.LOGGER_APPLICATION);			
		}finally{
			ConnectionUtils.closeConnection(conn);
		}
		
		return dto;
	}
	
	private void getUser(UserProfileDto dto, Connection conn) throws SQLException{
	
		utilGridInfoModel model = null;
        
		try{
			model = daoCommon.getAllUserActive(dto.getUserIdSearch(), dto.getUserNameSearch(), "", conn);
  	
	        if (model != null ) { 
	        	dto.setColumnField(model.getColunmName());
	        	dto.setColumn(model.getColumn());
	        	dto.setRow(model.getRow());
	        	dto.setUserProfile(model.getGridInfo());
	        	dto.setDeleteCheckBoxColumn(new String[dto.getRow()]);
	        } 
  
		}catch(SQLException e){
			LogWriter.error("userprofileBL [Method : getAllUser] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
			throw new SQLException();
		}
	}	

	public GenericDto searchEvent(UserProfileDto dto) throws Exception{
		LogWriter.debug("<=====Start search(UserProfile) Event=====>", Constant.LOGGER_APPLICATION);
		
		conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO);
		
		try{			
			LogWriter.debug("Step 1 : Get user by condition", Constant.LOGGER_APPLICATION);	
			getUser(dto, conn);
			
			if (dto.getRow() == 0){
				// Don't have data.
				dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_NOT_FOUND, null);
				if(!dto.getUserIdSearch().equals("")) dto.setUserIdSearch_Attr(Constant.ERR_ATTR);
				if(!dto.getUserNameSearch().equals("")) dto.setUserNameSearch_Attr(Constant.ERR_ATTR);
					
			}else{
				// Have data ==> set hidden userId, userName for export EXCEL.
				dto.setHiduserIdsearch(dto.getUserIdSearch());
				dto.setHiduserNameSearch(dto.getUserNameSearch());
			}
			
			dto.setService_id("userprofile");
			
		}catch(Exception e){
			LogWriter.error("userprofileBL [Method : searchEvent] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
		}finally{
			ConnectionUtils.closeConnection(conn);
		}
	
		return dto;	
	}
	
	public GenericDto addEvent(UserProfileDto dto) throws Exception{
		// Redirect userprofile_add.jsp	
		LogWriter.info("<=====Start add(UserProfile) Event=====>", Constant.LOGGER_APPLICATION);
		
		UserProfileAddEditDto genericDto = null;
		EventFactory event = new EventFactory();
		genericDto = new UserProfileAddEditDto();
		genericDto.populate(dto);
		
		genericDto = (UserProfileAddEditDto) event.executeBL("userprofileaddedit", "init", genericDto);
		return genericDto;
				
	}
	
	public GenericDto editEvent(UserProfileDto dto) throws Exception{
		// Redirect userprofile_add.jsp
		LogWriter.info("<=====Start edit(UserProfile) Event=====>", Constant.LOGGER_APPLICATION);
		
		UserProfileAddEditDto genericDto = null;
		EventFactory event = new EventFactory();
		genericDto = new UserProfileAddEditDto();
		genericDto.populate(dto);
		// Move hidUserId to Edit Page
		genericDto.setUseridSearch(dto.getHidUseIdEdit());
		genericDto = (UserProfileAddEditDto) event.executeBL("userprofileaddedit", "edit", genericDto);
	
		
		return genericDto;
				
	}
	
	public GenericDto deleteEvent(UserProfileDto dto) throws Exception{
		
		LogWriter.info("<=====Start delete(UserProfile) Event=====>", Constant.LOGGER_APPLICATION);
		
		conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO);
		
		try{
			LogWriter.info("Step 1 : Loop delete by userId", Constant.LOGGER_APPLICATION);
			for (int i = 0; i < dto.getDeleteCheckBoxColumn().length; i++) {
				if (!("").equals(dto.getDeleteCheckBoxColumn()[i])){
					dao.deleteUser(dto.getDeleteCheckBoxColumn()[i], dto.getUserId(), conn);
				}				
			}
			
			LogWriter.info("Step 2 : Refresh by Get all user", Constant.LOGGER_APPLICATION);
			getUser(dto, conn);
			
			// Forward JSP.
			dto.setService_id("userprofile");
			
		}catch(Exception e){
			LogWriter.error("userprofileBL [Method : deleteEvent] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
		}finally{
			ConnectionUtils.closeConnection(conn);
		}
		return dto;				
	}
	
	public GenericDto exportExcelEvent(UserProfileDto dto) throws Exception{
		
		LogWriter.info("<=====Start exportExcel(UserProfile) Event=====>", Constant.LOGGER_APPLICATION);
		
		conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO);
		
		utilGridInfoModel model = null;
		
		try{
			LogWriter.debug("Hid-UserIdSearch : " + dto.getHiduserIdsearch(), Constant.LOGGER_APPLICATION);
			LogWriter.debug("Hid-UserNameSearch : " + dto.getHiduserNameSearch(), Constant.LOGGER_APPLICATION);
			model = daoCommon.getAllUserActive(dto.getHiduserIdsearch(), dto.getHiduserNameSearch(), "", conn);
	  	
	        if (model.getRow() > 0 ) { 
		        dto.setWorkbook(createWorkbook(model));		        		               	        	
	        } 
	        if(dto.getWorkbook() == null){
	        	getUser(dto, conn);
	        }
	        dto.setService_id("userprofile");
			
		}catch(Exception e){
			LogWriter.error("userprofileBL [Method : searchEvent] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
		}finally{
			ConnectionUtils.closeConnection(conn);
		}
		return dto;				
	}
	
	public HSSFWorkbook createWorkbook(utilGridInfoModel usermodel) throws Exception {
		
		int column = usermodel.getColumn();
		int row_data = usermodel.getRow();

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("User Profile");

        /**
         * Setting the width columns.
         */
        sheet.setColumnWidth(0, 3000);
        sheet.setColumnWidth(1, 8000);
        for (int i = 9; i < column; i++) {
        	sheet.setColumnWidth((i-7), 5500);
		}
        

        /**
         * Style for the header cells.
         */
        HSSFCellStyle headerCellStyle = wb.createCellStyle();
        HSSFFont boldFont = wb.createFont();
        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerCellStyle.setFont(boldFont);
        headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);   
        headerCellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);  
        
        /**
         * Set colunmName on row[0]
         */
        HSSFRow row = sheet.createRow(0);
        /*row[0] , column[0] => userId*/
        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(headerCellStyle);
        cell.setCellValue(new HSSFRichTextString(usermodel.getColunmName()[0]));
        /*row[0] , column[1] => userName*/
        cell = row.createCell(1);
        cell.setCellStyle(headerCellStyle);
        cell.setCellValue(new HSSFRichTextString(usermodel.getColunmName()[1]));
        for (int i = 9; i < column; i++) {
        	/*row[0] , column[2] to N => MenuName*/
        	cell = row.createCell((i-7));
            cell.setCellStyle(headerCellStyle);
            cell.setCellValue(new HSSFRichTextString(usermodel.getColunmName()[i]));
		}
        

        /**
         * Set value by each row.
         */
        for (int index = 0; index < row_data; index++) {
            row = sheet.createRow(index+1);
            /*UserId*/
            cell = row.createCell(0);
            HSSFRichTextString UserId = new HSSFRichTextString(usermodel.getGridInfo()[index][0]);
            cell.setCellValue(UserId);
            /*UserName*/
            cell = row.createCell(1);
            HSSFRichTextString UserName = new HSSFRichTextString(usermodel.getGridInfo()[index][1] );
            cell.setCellValue(UserName);
            /*Loop menuName*/
            HSSFCellStyle menuCellStyle = wb.createCellStyle();
            menuCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            for (int i = 9; i < column; i++) {
            	cell = row.createCell(i-7);
                HSSFRichTextString menuName = new HSSFRichTextString(usermodel.getGridInfo()[index][i]);
                cell.setCellStyle(menuCellStyle);
                cell.setCellValue(menuName);	
			}
            
        }
        return wb;
    }
	

}
